GETSUBSTR Function for LibreOffice Calc

GETSUBSTR() Description

The GETSUBSTR() function is intended for selecting part of the text by the given separator.

The GETSUBSTR function code splits the text into an array by the specified separator and returns the element of the given array according to the specified serial number.

StarBASIC Code for GETSUBSTR

Below is the macro code to create a custom function GETSUBSTR that can split the text into parts and retrieve any element of it in the LibreOffice program Calc.

To add a new function, open the menu Tools - Macros - Edit Macros..., select Module1 and copy the following text to the module:

Function GETSUBSTR(Txt, Delimiter, n) As String  
   Dim txtArray As Variant  
     
     If Txt = "" Then Exit Function
     
      txtArray = Split(Txt, Delimiter) 
      maxExt = UBound(txtArray)
      
      If n >= 0 Then 
        normExt = n - 1
      Else      
        normExt = maxExt + n + 1
      EndIf      
       
      extToFind = Max(Min(maxExt, normExt), 0)
      
      GETSUBSTR = txtArray(extToFind)
     
End Function

Then, close Macro Editor, return to LibreOffice Calc and use the new function in any cell.

Using the extension

You can also use the feature GETSUBSTR() by installing the free extension YouLibreCalc.oxt or its full-featured version YLC_Utilities.oxt .

After that, this function will be available in all files that will be opened in LibreOffice Calc.